package com.aier.school.service.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Component;

import com.aier.common.dao.ICommonDAO;
import com.aier.school.entity.TeacherAttendanceMonth;
import com.aier.school.entity.TeacherIcardInfo;
import com.aier.school.entity.TeacherIcardInfoState;
import com.aier.school.entity.mapper.TeacherAttendanceMonthRowMapper;
import com.aier.school.entity.mapper.TeacherIcardInfoRowMapper;
import com.aier.school.entity.mapper.TeacherIcardInfoStateRowMapper;
import com.aier.school.service.ISchoolMonAttendService;

@Component("schoolMonAttendService")
public class SchoolMonAttendServiceImpl_J implements ISchoolMonAttendService{

    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    
    @Override
    public TeacherAttendanceMonth searchAttMontByTaid(Integer taid){
        return (TeacherAttendanceMonth) commonDao.listByObject(
                "select * from teacher_attendance_month where taid=?", new Object[] {taid}, new TeacherAttendanceMonthRowMapper());
    }
    
    
    @Override
    public Map<String, Object> searchAttMonthsByCondition(int page,
            int pagesize, int sid,List<Long> tids, Integer year_begin, Integer month_begin,
            Integer year_end, Integer month_end){

        String sql = " from teacher_attendance_month as tam where tam.tid in (select tid from school_teacher where sid=?) ";
        if (null != year_begin){
            sql += " and tam.year>=" + year_begin;
        }
        if (null != month_begin){
            sql += " and tam.month>=" + month_begin;
        }
        if (null != year_end){
            sql += " and tam.year<=" + year_end;
        }
        if (null != month_end){
            sql += " and tam.month<=" + month_end;
        }
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tam.tid in " + temp_tids;
        }
        String sql_first = sql;
        sql += " order by tam.tid desc";

        return commonDao
                .queryPage(
                        "select tam.*,(select tname from school_teacher where tid=tam.tid) as teacherName," +
                        "(select name from school_grade sc where sc.gid=(select gid from school_teacher where tid=tam.tid)) as typeName, "
                                + "(select sum(tl.leaveTime) from teacher_leave tl where tl.type_handle=3 and " +
                                "tl.tid=tam.tid and YEAR(tl.sdate)=tam.year and MONTH(tl.sdate)=tam.month) as time_exc  " 
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new TeacherAttendanceMonthRowMapper(TeacherAttendanceMonthRowMapper.AND_TEACHERNAME));        
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherAttendanceMonth> searchAttMonthsByCondition(int sid,
            List<Long> tids, Integer year_begin, Integer month_begin,
            Integer year_end, Integer month_end){

        String sql = " from teacher_attendance_month as tam where tam.tid in (select tid from school_teacher where sid=?) ";
        if (null != year_begin){
            sql += " and tam.year>=" + year_begin;
        }
        if (null != month_begin){
            sql += " and tam.month>=" + month_begin;
        }
        if (null != year_end){
            sql += " and tam.year<=" + year_end;
        }
        if (null != month_end){
            sql += " and tam.month<=" + month_end;
        }
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tam.tid in " + temp_tids;
        }
        sql += " order by tam.year desc";
        return (List<TeacherAttendanceMonth>) commonDao
                .list( "select tam.*,(select tname from school_teacher where tid=tam.tid) as teacherName," +
                        "(select name from school_grade sc where sc.gid=(select gid from school_teacher where tid=tam.tid)) as typeName, "
                                + "(select sum(tl.leaveTime) from teacher_leave tl where tl.type_handle=3 and " +
                                "tl.tid=tam.tid and YEAR(tl.sdate)=tam.year and MONTH(tl.sdate)=tam.month) as time_exc  " 
                                + sql, new Object[] { sid },
                        new TeacherAttendanceMonthRowMapper(TeacherAttendanceMonthRowMapper.AND_TEACHERNAME));  
    }
    
    @Override
    public Map<String, Object> searchTeacherAttMonthsByCondition(int page,
            int pagesize, int sortable, int tid,
            Integer year_begin, Integer month_begin,
            Integer year_end, Integer month_end){
        String sql = " from teacher_attendance_month as tam where tam.tid=? ";
        if (null != year_begin){
            sql += " and tam.year>=" + year_begin;
        }
        if (null != month_begin){
            sql += " and tam.month>=" + month_begin;
        }
        if (null != year_end){
            sql += " and tam.year<=" + year_end;
        }
        if (null != month_end){
            sql += " and tam.month<=" + month_end;
        }

        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_NAME:
                sql += " order by teacherName";
                break;
            case TEACHER_SORTABLE_NAME_DESC:
                sql += " order by teacherName desc";
                break;
            case TEACHER_SORTABLE_TYPENAME:
                sql += " order by typeName";
                break;
            case TEACHER_SORTABLE_TYPENAME_DESC:
                sql += " order by typeName desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tam.*,(select name from teacher where tid=tam.tid) as teacherName," +
                        "(select name from school_grade sc where sc.gid=(select gid from teacher where tid=tam.tid)) as typeName, " + 
                        "(select sum(tl.leaveTime) from teacher_leave tl where tl.type_handle=3 and tl.tid=tam.tid " +
                        "and YEAR(tl.sdate)=tam.year and MONTH(tl.sdate)=tam.month) as time_exc  " + 
                        sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { tid }, new Object[] { tid },
                        new TeacherAttendanceMonthRowMapper(TeacherAttendanceMonthRowMapper.AND_TEACHERNAME));    
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherAttendanceMonth> searchTeacherAttMonthsByCondition(int tid, 
            Integer year_begin, Integer month_begin){
        String sql = " from teacher_attendance_month as tam where tam.tid=? ";
        if (null != year_begin){
            sql += " and tam.year=" + year_begin;
        }
        if (null != month_begin){
            sql += " and tam.month=" + month_begin;
        }

        return (List<TeacherAttendanceMonth>) commonDao.list(
                        "select tam.*,(select name from teacher where tid=tam.tid) as teacherName," +
                        "(select name from school_grade sc where sc.gid=(select gid from teacher where tid=tam.tid)) as typeName, " + 
                        "(select sum(tl.leaveTime) from teacher_leave tl where tl.type_handle=3 and tl.tid=tam.tid " +
                        "and YEAR(tl.sdate)=tam.year and MONTH(tl.sdate)=tam.month) as time_exc  " + 
                        sql, new Object[] { tid },
                        new TeacherAttendanceMonthRowMapper(TeacherAttendanceMonthRowMapper.AND_TEACHERNAME));         
    }
    
    @Override
    public Map<String, Object> searchAttDetails(int page, int pagesize,
            int sortable, int sid, Long uid,Integer taid){

        String sql = " from teacher_icard_info as tii where tii.tid = (select tid from teacher_attendance_month where taid=?) ";
        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_NAME:
                sql += " order by teacherName";
                break;
            case TEACHER_SORTABLE_NAME_DESC:
                sql += " order by teacherName desc";
                break;
            case TYPE_SORTABLE_INOUT:
                sql += " order by tii.type";
                break;
            case TYPE_SORTABLE_INOUT_DESC:
                sql += " order by tii.type desc";
                break;
            case CDATE_SORTABLE:
                sql += " order by tii.cdate";
                break;
            case CDATE_SORTABLE_DESC:
                sql += " order by tii.cdate desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tii.*,(select name from teacher where tid=tii.tid) as teacherName "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { taid }, new Object[] { taid },
                        new TeacherIcardInfoRowMapper(TeacherIcardInfoRowMapper.AND_TEACHERNAME));        
    }
    
    @Override
    public Map<String, Object> searchAttDetailsByCondition(int page,int pagesize, int sid,
            List<Long> tids, String dayBeginValue, String dayEndValue){
        String sql = " from teacher_icard_info as tii where tii.tid in (select tid from school_teacher where sid=?) ";
        
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tii.tid in " + temp_tids;
        }
        if ((dayBeginValue==dayEndValue)&&(dayBeginValue!=null)&&(!"".equals(dayBeginValue.trim()))){
            sql += " and tii.cdate='" + dayBeginValue + "'";
        }else{
            if (dayBeginValue!=null && !"".equals(dayBeginValue.trim())){
                sql += " and tii.cdate>='" + dayBeginValue + "'";
            }
            if (dayEndValue!=null && !"".equals(dayEndValue.trim())){
                sql += " and tii.cdate<='" + dayEndValue + "'";
            }            
        }
        String sql_first = sql;
        sql += " order by tii.cdate desc";
        return commonDao
                .queryPage(
                        "select tii.*,(select tname from school_teacher where tid=tii.tid) as teacherName "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new TeacherIcardInfoRowMapper(TeacherIcardInfoRowMapper.AND_TEACHERNAME));    
        
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherIcardInfo> searchAttDetailsByCondition(int sid,
            List<Long> tids, Integer year_begin, Integer month_begin,
            Integer year_end, Integer month_end){

        String sql = " from teacher_icard_info as tii where tii.tid in (select tid from school_teacher where sid=?) ";
        
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tii.tid in " + temp_tids;
        }
        if (null != year_begin){
            sql += " and YEAR(tii.cdate)>=" + year_begin;
        }
        if (null != month_begin){
            sql += " and MONTH(tii.cdate)>=" + month_begin;
        }
        if (null != year_end){
            sql += " and YEAR(tii.cdate)<=" + year_end;
        }
        if (null != month_end){
            sql += " and MONTH(tii.cdate)<=" + month_end;
        }
        sql += " order by tii.cdate desc";

        return (List<TeacherIcardInfo>) commonDao
                .list(
                        "select tii.*,(select tname from school_teacher where tid=tii.tid) as teacherName "
                                + sql, new Object[] { sid }, 
                        new TeacherIcardInfoRowMapper(TeacherIcardInfoRowMapper.AND_TEACHERNAME));         
    }
    
    @Override
    public Map<String, Object> searchTeacherAttDetailsByCondition(int page,
            int pagesize, int sortable, int tid,
            String dayBeginValue, String dayEndValue){
        String sql = " from teacher_icard_info as tii where tii.tid=? ";
    
        if ((dayBeginValue==dayEndValue)&&dayBeginValue!=null&&(!"".equals(dayBeginValue.trim()))){
            sql += " and tii.cdate='" + dayBeginValue + "'";
        }else{
            if (dayBeginValue!=null&&!"".equals(dayBeginValue.trim())){
                sql += " and tii.cdate>='" + dayBeginValue + "'";
            }
            if (dayEndValue!=null&&!"".equals(dayEndValue.trim())){
                sql += " and tii.cdate<='" + dayEndValue + "'";
            }            
        }
        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_NAME:
                sql += " order by teacherName";
                break;
            case TEACHER_SORTABLE_NAME_DESC:
                sql += " order by teacherName desc";
                break;
            case TYPE_SORTABLE_INOUT:
                sql += " order by tii.type";
                break;
            case TYPE_SORTABLE_INOUT_DESC:
                sql += " order by tii.type desc";
                break;
            case CDATE_SORTABLE:
                sql += " order by tii.cdate";
                break;
            case CDATE_SORTABLE_DESC:
                sql += " order by tii.cdate desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tii.*,(select name from teacher where tid=tii.tid) as teacherName "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { tid }, new Object[] { tid },
                        new TeacherIcardInfoRowMapper(TeacherIcardInfoRowMapper.AND_TEACHERNAME));            
        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherIcardInfo> searchTeacherAttDetailsByCondition(int tid,
            String dayBeginValue){
        String sql = " from teacher_icard_info as tii where tii.tid=? ";
        if (dayBeginValue!=null&&!"".equals(dayBeginValue.trim())){
            sql += " and DATE(tii.cdate)='" + dayBeginValue + "'";
        }
        return (List<TeacherIcardInfo>) commonDao.list(
                        "select tii.*,(select name from teacher where tid=tii.tid) as teacherName "
                                + sql, new Object[] { tid }, 
                        new TeacherIcardInfoRowMapper(TeacherIcardInfoRowMapper.AND_TEACHERNAME));           
    }
    
    @Override
    @SuppressWarnings("unchecked")
    public List<TeacherIcardInfoState> searchTeacherIcardInfoStates(int tid,
            Integer year_begin, Integer month_begin){
        String sql = "select tiis.* from teacher_icard_info_state tiis where tiis.tid=? ";
        
        if (null != year_begin){
            sql += " and YEAR(tiis.cdate)='" + year_begin + "'";
        }
        if (null != month_begin){
            sql += " and MONTH(tiis.cdate)='" + month_begin + "'";
        }            

        return (List<TeacherIcardInfoState>) commonDao.list(
                        sql, new Object[] { tid },new TeacherIcardInfoStateRowMapper(TeacherIcardInfoStateRowMapper.AND_DATENAME));  
    }
    

    
    
}
